<?php

namespace App\Repositories\Analysis;

use App\Facade\AppContext;
use App\Models\BaseModel;
use App\Models\Ca\Customer;
use App\Models\Ca\Promotion;
use App\Repositories\BaseRepository;
use App\Utils\Output;
use App\Utils\TimeUtil;
use Illuminate\Support\Arr;

class CallerRepository extends BaseRepository
{
    /**
     * 分页查询
     * @param array $params
     * @return Output
     */
    function adminPager(array $params)
    {
        extract($params);
        $query = Customer::query()
            ->where('hospital_id', '=', AppContext::hospitalId());
        $query->leftJoin('system__users as medical', 'medical.id', '=', 'ca__customers.medical_id');
        $query->select(
            'ca__customers.medical_id as id',
            'ca__customers.effective as effective_text'
        );
        if (!empty($month)) {
            $year = Arr::get($params, 'year', date('Y'));
            $start = $year . '-' . str_pad($month, 2, '0', STR_PAD_LEFT) . '-01';
            $end = date('Y-m-d', strtotime($start . ' +1 month -1day')) . ' 23:59:59';
            $query->whereBetween('ca__customers.jz_time', [$start, $end]);
        } else {
            $range = TimeUtil::thisMonth();
            $start = date('Y-m-d H:i:s', $range['begin']);
            $end = date('Y-m-d H:i:s', $range['end']);
            $query->whereBetween('ca__customers.jz_time', [$start, $end]);
        }
        $query->selectRaw('count(*) count');
        $query->selectRaw('max(medical.name) name');
        $query->groupBy('ca__customers.medical_id');
        $totalQuery = Customer::withTrashed()->fromSub($query, 'tj');
        $total = $totalQuery->count();
        $lis = $this->setParams($query, $params, ['count' => 'desc', 'id' => 'desc']);
        return Output::pager($lis, $total);
    }
    function effective(array $params){
        extract($params);
        
        $query = Customer::query()
        ->where('hospital_id', '=', AppContext::hospitalId()); 
        $query->leftJoin('system__users as medical', 'medical.id', '=', 'ca__customers.medical_id');
        $start=date('Y-m-d');
        $end=$start;
        if (!empty($date_range)) {
            list($start, $end) = explode(' ~ ', $date_range);  
        }
        $query->select(
            'medical.username'
        );
        $query->selectRaw("SUM(case when ca__customers.`created_at` BETWEEN '".$start."' AND '".$end." 23:59:59' then 1 else 0 END) dj"); 
        $query->selectRaw("SUM(case when ca__customers.`tady_yy_time` BETWEEN '".$start."' AND '".$end." 23:59:59' then 1 else 0 END) yy"); 
        $query->selectRaw("SUM(case when ca__customers.`jz_time` BETWEEN '".$start."' AND '".$end." 23:59:59' then 1 else 0 END) dz"); 
        $query->selectRaw("SUM(case when ca__customers.`spend_time`  BETWEEN '".$start."' AND '".$end." 23:59:59' then 1 else 0 END) xf"); 
        $query->selectRaw("SUM(case when ca__customers.`created_at` BETWEEN '".$start."' AND '".$end." 23:59:59'and ca__customers.`ztype`=1 then 1 else 0 END) td"); 
        $query->selectRaw("SUM(case when ca__customers.`created_at` BETWEEN '".$start."' AND '".$end." 23:59:59'and ca__customers.`ztype`=2 then 1 else 0 END) tw"); 
        $query->selectRaw("SUM(case when ca__customers.`created_at` BETWEEN '".$start."' AND '".$end." 23:59:59'and ca__customers.`consultation_type`=1 then 1 else 0 END) wcn");
        $query->groupBy('medical.username');
        $lis=$query->get();
        return Output::pager($lis, 1);
    }
    function promotion(array $params)
    { 
        extract($params);
		
        $query = Customer::query()
        ->where('hospital_id', '=', AppContext::hospitalId()); 
  //       if (!array_key_exists('month',$params)) {
  //           $year=date("Y");
  //           $month=date("m");
  //       }    
		
		// $month2=0;
		// if($month<10){
		// 	$month2='0'.$month;
		// }else{
		// 	$month2=$month;
		// }
		$start=date('Y-m-d');
        $end=$start;
        if (!empty($date_range)) {
            list($start, $end) = explode(' ~ ', $date_range);  
        }
        $query->whereBetween('created_at', [$start, $end.' 23:59:59']);  
        $query->selectRaw('DAY(created_at) AS day');
        $query->selectRaw('COUNT(*) AS dj');
        $query->selectRaw('count(case when ztype=1 then 1 else 0 END) td');
        $query->selectRaw('count(case when ztype=2 then 1 else 0 END) tw');
        $query->selectRaw("sum(case when tady_yy_time BETWEEN '".$start."' AND '".$end." 23:59:59' then 1 else 0 END) yy"); 
        $query->selectRaw("sum(case when jz_time BETWEEN '".$start."' AND '".$end." 23:59:59' then 1 else 0 END) jz"); 
        // $query->selectRaw('sun(if(DATE_FORMAT(tady_yy_time,"%Y-%m") = "'.$year.'-'.$month2.'",1,0))yy');
        // $query->selectRaw('sun(if(DATE_FORMAT(jz_time,"%Y-%m") ="'.$year.'-'.$month2.'",1,0))jz');
        $query->groupBy('day');
        $lis=$query->get();

        $query2=Promotion::query()
        ->where('hospital_id', '=', AppContext::hospitalId())  
        ->whereBetween('ctime', [$start, $end.' 23:59:59']) 
        ->selectRaw('DAY(ctime) AS day');
		$query2->selectRaw('zhanshi');
		$query2->selectRaw('dianji');
		$query2->selectRaw('duihua');
		$query2->selectRaw('money');
        $lis2=$query2->get();   
        foreach ($lis as $key => $v) {
           foreach ($lis2 as $key2 => $v2) {
              if($v['day']==$v2['day']){
                $v['zhanshi']=$v2['zhanshi'];
                $v['dianji']=$v2['dianji'];
                $v['duihua']=$v2['duihua'];
				$v['money']=$v2['money'];
                 unset($lis2[$key2]);
              }
           }
        }

        return Output::pager($lis, 1);
        
    }

    function getModel(): BaseModel
    {
        return new Customer();
    }
}